Public Class LabPrice
    Protected _year As String
    Protected _Organization As New Organization()
    Protected _LabType As New LabType()
    Protected _lab_price As Double
    Protected _Data As ArrayList
    Protected _DataCurrent As Integer

    Public Property year() As String
        Get
            Return _year
        End Get
        Set(ByVal value As String)
            _year = value
        End Set
    End Property

    Public Property Organization() As Organization
        Get
            Return _Organization
        End Get
        Set(ByVal value As Organization)
            _Organization = value
        End Set
    End Property

    Public Property LabType() As LabType
        Get
            Return _LabType
        End Get
        Set(ByVal value As LabType)
            _LabType = value
        End Set
    End Property

    Public Property lab_price() As Double
        Get
            Return _lab_price
        End Get
        Set(ByVal value As Double)
            _lab_price = value
        End Set
    End Property

    Public Function getLabPriceYear() As System.Data.DataSet
        Dim decryptText As New DateUtilities.Encrypt_Data()
        Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
            decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
            "PoliceHealthConnectionString").ToString(), True))
        Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
        objCmd.Connection = objConn
        objCmd.CommandType = CommandType.StoredProcedure
        objCmd.CommandText = "proc_SELECT_LabPriceYear"

        Dim objDa As New MySql.Data.MySqlClient.MySqlDataAdapter(objCmd)
        Dim objDs As New System.Data.DataSet()
        objDa.Fill(objDs, "LabPriceYear")

        Return objDs
    End Function

    Protected Function getsLabPriceYear() As ArrayList
        Dim outResult As New ArrayList()
        Dim decryptText As New DateUtilities.Encrypt_Data()
        Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
            decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
            "PoliceHealthConnectionString").ToString(), True))
        Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
        objCmd.Connection = objConn
        objCmd.CommandType = CommandType.StoredProcedure
        objCmd.CommandText = "proc_SELECT_LabPriceYear"

        Dim objDa As New MySql.Data.MySqlClient.MySqlDataAdapter(objCmd)
        Dim objDs As New System.Data.DataSet()
        objDa.Fill(objDs, "LabPriceYear")

        For Each objDr As System.Data.DataRow In objDs.Tables("LabPriceYear").Rows
            Dim obj As LabPrice = New LabPrice()
            obj.year = objDr.Item("year").ToString()
            outResult.Add(obj)
        Next

        Return outResult
    End Function

    Public Function getLabPriceDetailByYear(ByVal pYear As String, ByVal pProvinceID As String) As System.Data.DataSet
        Dim decryptText As New DateUtilities.Encrypt_Data()
        Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
            decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
            "PoliceHealthConnectionString").ToString(), True))
        Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
        objCmd.Connection = objConn
        objCmd.CommandType = CommandType.StoredProcedure
        objCmd.CommandText = "proc_SELECT_LabPriceDetailByYear"

        Dim objParam As New MySql.Data.MySqlClient.MySqlParameter("inYear", pYear)
        objCmd.Parameters.Add(objParam)

        objParam = New MySql.Data.MySqlClient.MySqlParameter("inProvinceID", pProvinceID)
        objCmd.Parameters.Add(objParam)

        Dim objDa As New MySql.Data.MySqlClient.MySqlDataAdapter(objCmd)
        Dim objDs As New System.Data.DataSet()
        objDa.Fill(objDs, "LabPriceDetailByYear")

        Return objDs
    End Function

    Protected Function getsLabPriceDetailByYear(ByVal pYear As String, ByVal pProvinceID As String) As ArrayList
        Dim outResult As New ArrayList()
        Dim decryptText As New DateUtilities.Encrypt_Data()
        Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
            decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
            "PoliceHealthConnectionString").ToString(), True))
        Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
        objCmd.Connection = objConn
        objCmd.CommandType = CommandType.StoredProcedure
        objCmd.CommandText = "proc_SELECT_LabPriceDetailByYear"

        Dim objParam As New MySql.Data.MySqlClient.MySqlParameter("inYear", pYear)
        objCmd.Parameters.Add(objParam)

        objParam = New MySql.Data.MySqlClient.MySqlParameter("inProvinceID", pProvinceID)
        objCmd.Parameters.Add(objParam)

        Dim objDa As New MySql.Data.MySqlClient.MySqlDataAdapter(objCmd)
        Dim objDs As New System.Data.DataSet()
        objDa.Fill(objDs, "LabPriceDetailByYear")

        For Each objDr As System.Data.DataRow In objDs.Tables("LabPriceDetailByYear").Rows
            Dim obj As LabPrice = New LabPrice()
            obj.year = objDr.Item("year").ToString()
            obj.LabType.lab_type_id = objDr.Item("lab_type_id").ToString()
            obj.LabType.lab_type_name = objDr.Item("lab_type_name").ToString()
            obj.lab_price = objDr.Item("lab_price").ToString()
            outResult.Add(obj)
        Next

        Return outResult
    End Function

    Public Function getLabPriceDetailByYearAndID(ByVal pYear As String, ByVal pLabID As Integer) As System.Data.DataSet
        Dim decryptText As New DateUtilities.Encrypt_Data()
        Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
            decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
            "PoliceHealthConnectionString").ToString(), True))
        Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
        objCmd.Connection = objConn
        objCmd.CommandType = CommandType.StoredProcedure
        objCmd.CommandText = "proc_SELECT_LabPriceDetailByYearAndID"

        Dim objParam As New MySql.Data.MySqlClient.MySqlParameter("inYear", pYear)
        objCmd.Parameters.Add(objParam)

        objParam = New MySql.Data.MySqlClient.MySqlParameter("inLabID", pLabID)
        objCmd.Parameters.Add(objParam)

        Dim objDa As New MySql.Data.MySqlClient.MySqlDataAdapter(objCmd)
        Dim objDs As New System.Data.DataSet()
        objDa.Fill(objDs, "LabPriceDetailByYearAndID")

        Return objDs
    End Function

    Protected Function getsLabPriceDetailByYearAndID(ByVal pYear As String, ByVal pLabTypeID As Integer) As ArrayList
        Dim outResult As New ArrayList()
        Dim decryptText As New DateUtilities.Encrypt_Data()
        Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
            decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
            "PoliceHealthConnectionString").ToString(), True))
        Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
        objCmd.Connection = objConn
        objCmd.CommandType = CommandType.StoredProcedure
        objCmd.CommandText = "proc_SELECT_LabPriceDetailByYearAndID"

        Dim objParam As New MySql.Data.MySqlClient.MySqlParameter("inYear", pYear)
        objCmd.Parameters.Add(objParam)

        objParam = New MySql.Data.MySqlClient.MySqlParameter("inLabTypeID", pLabTypeID)
        objCmd.Parameters.Add(objParam)

        Dim objDa As New MySql.Data.MySqlClient.MySqlDataAdapter(objCmd)
        Dim objDs As New System.Data.DataSet()
        objDa.Fill(objDs, "LabPriceDetailByYearAndID")

        For Each objDr As System.Data.DataRow In objDs.Tables("LabPriceDetailByYearAndID").Rows
            Dim obj As LabPrice = New LabPrice()
            obj.year = objDr.Item("year").ToString()
            obj.Organization.organization_id = objDr.Item("organization_id").ToString()
            obj.LabType.lab_type_id = objDr.Item("lab_type_id").ToString()
            obj.LabType.lab_type_name = objDr.Item("lab_type_name").ToString()
            obj.lab_price = objDr.Item("lab_price").ToString()
            outResult.Add(obj)
        Next

        Return outResult
    End Function

    Public Function insertLabPrice() As Boolean
        Try
            Dim decryptText As New DateUtilities.Encrypt_Data()
            Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
                decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
                "PoliceHealthConnectionString").ToString(), True))
            Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
            objCmd.Connection = objConn
            objCmd.CommandType = CommandType.StoredProcedure
            objCmd.CommandText = "proc_INSERT_LabPrice"

            Dim objParam As New MySql.Data.MySqlClient.MySqlParameter("inYear", year)
            objCmd.Parameters.Add(objParam)

            objParam = New MySql.Data.MySqlClient.MySqlParameter("inOrganizationID", Organization.organization_id)
            objCmd.Parameters.Add(objParam)

            objParam = New MySql.Data.MySqlClient.MySqlParameter("inLabTypeID", LabType.lab_type_id)
            objCmd.Parameters.Add(objParam)

            objParam = New MySql.Data.MySqlClient.MySqlParameter("inLabPrice", lab_price)
            objCmd.Parameters.Add(objParam)

            objConn.Open()
            objCmd.ExecuteNonQuery()
            objConn.Close()

            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Function insertLabPriceAuto(ByVal pYear As String) As Boolean
        Try
            Dim decryptText As New DateUtilities.Encrypt_Data()
            Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
                decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
                "PoliceHealthConnectionString").ToString(), True))
            Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
            objCmd.Connection = objConn
            objCmd.CommandType = CommandType.StoredProcedure
            objCmd.CommandText = "proc_INSERT_LabPriceAuto"

            Dim objParam As New MySql.Data.MySqlClient.MySqlParameter("inYear", pYear)
            objCmd.Parameters.Add(objParam)

            objConn.Open()
            objCmd.ExecuteNonQuery()
            objConn.Close()

            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Function updateLabPrice(ByVal pProvinceID As String) As Boolean
        Try
            Dim decryptText As New DateUtilities.Encrypt_Data()
            Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
                decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
                "PoliceHealthConnectionString").ToString(), True))
            Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
            objCmd.Connection = objConn
            objCmd.CommandType = CommandType.StoredProcedure
            objCmd.CommandText = "proc_UPDATE_LabPrice"

            Dim objParam As New MySql.Data.MySqlClient.MySqlParameter("inYear", year)
            objCmd.Parameters.Add(objParam)

            objParam = New MySql.Data.MySqlClient.MySqlParameter("inLabTypeID", LabType.lab_type_id)
            objCmd.Parameters.Add(objParam)

            objParam = New MySql.Data.MySqlClient.MySqlParameter("inProvinceID", pProvinceID)
            objCmd.Parameters.Add(objParam)

            objParam = New MySql.Data.MySqlClient.MySqlParameter("inLabPrice", lab_price)
            objCmd.Parameters.Add(objParam)

            objConn.Open()
            objCmd.ExecuteNonQuery()
            objConn.Close()

            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Function deleteLabPrice(ByVal pProvinceID As String) As Boolean
        Try
            Dim decryptText As New DateUtilities.Encrypt_Data()
            Dim objConn As New MySql.Data.MySqlClient.MySqlConnection( _
                decryptText.getDecrypt(System.Configuration.ConfigurationManager.ConnectionStrings( _
                "PoliceHealthConnectionString").ToString(), True))
            Dim objCmd As New MySql.Data.MySqlClient.MySqlCommand()
            objCmd.Connection = objConn
            objCmd.CommandType = CommandType.StoredProcedure
            objCmd.CommandText = "proc_DELETE_LabPrice"

            Dim objParam As New MySql.Data.MySqlClient.MySqlParameter("inYear", year)
            objCmd.Parameters.Add(objParam)

            objParam = New MySql.Data.MySqlClient.MySqlParameter("inProvinceID", pProvinceID)
            objCmd.Parameters.Add(objParam)

            objConn.Open()
            objCmd.ExecuteNonQuery()
            objConn.Close()

            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Function NextRecord() As Boolean
        If ((Not _Data Is Nothing) AndAlso (((Not _Data Is Nothing) AndAlso _
            (_DataCurrent < _Data.Count And _Data.Count > 0)))) Then
            _year = _Data(_DataCurrent).year
            _Organization.organization_id = _Data(_DataCurrent).Organization.organization_id
            _LabType.lab_type_id = _Data(_DataCurrent).LabType.lab_type_id
            _LabType.lab_type_name = _Data(_DataCurrent).LabType.lab_type_name
            _lab_price = _Data(_DataCurrent).lab_price
            _DataCurrent += 1
            Return True
        End If
        Return False
    End Function

    Public Sub loadLabPriceYear()
        _Data = New ArrayList
        _DataCurrent = 0
        _Data = getsLabPriceYear()
    End Sub

    Public Sub loadLabPriceDetailByYear(ByVal pYear As String, ByVal pProvinceID As String)
        _Data = New ArrayList
        _DataCurrent = 0
        _Data = getsLabPriceDetailByYear(pYear, pProvinceID)
    End Sub

    Public Sub loadLabPriceDetailByYearAndID(ByVal pYear As String, ByVal pLabID As Integer)
        _Data = New ArrayList
        _DataCurrent = 0
        _Data = getsLabPriceDetailByYearAndID(pYear, pLabID)
    End Sub

End Class
